package pe.edu.upc.dai.grupo4.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import pe.edu.upc.dai.grupo4.model.CriterioConciliacion;
import pe.edu.upc.dai.grupo4.utils.ConnectionUtils;
import pe.edu.upc.dai.grupo4.utils.DaoException;

/**
 *
 * @author Tito Luyo Murata
 */
public class CriterioConciliacionDaoImpl implements CriterioConciliacionDao {

    public List<CriterioConciliacion> selectPorCodBancoCodTipoMovimientoBancario(int CodBanco, int CodTipoMovimientoBancario) {
        List<CriterioConciliacion> result = new ArrayList<CriterioConciliacion>();
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            connection = ConnectionUtils.getConnection();
            st = connection.prepareStatement("{call dbo.usp_CriteriosConciliacion_SEL_x_CodBanco_CodTipoMovimientoBancario(?,?)}");
            st.setInt(1, CodBanco);
            st.setInt(2, CodTipoMovimientoBancario);
            rs = st.executeQuery();

            while (rs.next()) {
                result.add(this.getData(rs));
            }
            rs.close();
            st.close();
        } catch (SQLException e) {
            throw new DaoException(e);
        } finally {
            ConnectionUtils.closeResultSet(rs);
            ConnectionUtils.closeStatement(st);
            ConnectionUtils.closeConnection(connection);
        }
        return result;
    }

    public CriterioConciliacion selectPorCodBancoCodTipoMovimientoBancarioPorOrden(int CodBanco, int CodTipoMovimientoBancario, int Orden) {
        CriterioConciliacion result = null;
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            connection = ConnectionUtils.getConnection();
            st = connection.prepareStatement("{call dbo.usp_CriteriosConciliacion_SEL_x_CodBanco_CodTipoMovimientoBancario_Orden(?,?,?)}");
            st.setInt(1, CodBanco);
            st.setInt(2, CodTipoMovimientoBancario);
            st.setInt(3, Orden);
            rs = st.executeQuery();

            while (rs.next()) {
                result = this.getData(rs);
            }
            rs.close();
            st.close();
        } catch (SQLException e) {
            throw new DaoException(e);
        } finally {
            ConnectionUtils.closeResultSet(rs);
            ConnectionUtils.closeStatement(st);
            ConnectionUtils.closeConnection(connection);
        }
        return result;
    }

    public int insertar(CriterioConciliacion criterioConciliacion) {
        int result = 0;
        Connection connection = null;
        CallableStatement st = null;
        try {
            connection = ConnectionUtils.getConnection();
            st = connection.prepareCall("{call usp_CriteriosConciliacion_INS(?,?,?,?,?)}");
            st.registerOutParameter(3, java.sql.Types.INTEGER);
            this.comun(st, criterioConciliacion);
            st.execute();
            ConnectionUtils.commit(connection);
            result = st.getInt(3);
        } catch (SQLException e) {
            ConnectionUtils.rollback(connection);
            throw new DaoException(e);
        } finally {
            ConnectionUtils.closeStatement(st);
            ConnectionUtils.closeConnection(connection);
        }
        return result;
    }

    public int actualizar(CriterioConciliacion criterioConciliacion) {
        int result = 0;
        Connection connection = null;
        CallableStatement st = null;
        try {
            connection = ConnectionUtils.getConnection();
            st = connection.prepareCall("{call dbo.usp_CriteriosConciliacion_UPD(?,?,?,?,?)}");
            st.setInt(3, criterioConciliacion.getOrden());
            this.comun(st, criterioConciliacion);
            st.execute();
            ConnectionUtils.commit(connection);
            result = st.getUpdateCount();
        } catch (SQLException e) {
            ConnectionUtils.rollback(connection);
            throw new DaoException(e);
        } finally {
            ConnectionUtils.closeStatement(st);
            ConnectionUtils.closeConnection(connection);
        }
        return result;
    }

    public void eliminar(CriterioConciliacion criterioConciliacion) {
        Connection connection = null;
        CallableStatement st = null;
        try {
            connection = ConnectionUtils.getConnection();
            st = connection.prepareCall("{call dbo.usp_CriteriosConciliacion_DEL(?,?,?)}");
            st.setInt(1, criterioConciliacion.getCodBanco());
            st.setInt(2, criterioConciliacion.getCodTipoMovimientoBancario());
            st.setInt(3, criterioConciliacion.getOrden());
            st.execute();
            ConnectionUtils.commit(connection);
        } catch (SQLException e) {
            ConnectionUtils.rollback(connection);
            throw new DaoException(e);
        } finally {
            ConnectionUtils.closeStatement(st);
            ConnectionUtils.closeConnection(connection);
        }
    }

    public int subir(CriterioConciliacion criterioConciliacion) {
        int result = 0;
        Connection connection = null;
        CallableStatement st = null;
        try {
            connection = ConnectionUtils.getConnection();
            st = connection.prepareCall("{call dbo.usp_CriteriosConciliacion_Subir(?,?,?,?)}");
            st.setInt(1, criterioConciliacion.getCodBanco());
            st.setInt(2, criterioConciliacion.getCodTipoMovimientoBancario());
            st.setInt(3, criterioConciliacion.getOrden());
            st.registerOutParameter(4, java.sql.Types.INTEGER);
            st.execute();
            ConnectionUtils.commit(connection);
            result = st.getInt(4);
        } catch (SQLException e) {
            ConnectionUtils.rollback(connection);
            throw new DaoException(e);
        } finally {
            ConnectionUtils.closeStatement(st);
            ConnectionUtils.closeConnection(connection);
        }
        return result;
    }

    public int bajar(CriterioConciliacion criterioConciliacion) {
        int result = 0;
        Connection connection = null;
        CallableStatement st = null;
        try {
            connection = ConnectionUtils.getConnection();
            st = connection.prepareCall("{call dbo.usp_CriteriosConciliacion_Bajar(?,?,?,?)}");
            st.setInt(1, criterioConciliacion.getCodBanco());
            st.setInt(2, criterioConciliacion.getCodTipoMovimientoBancario());
            st.setInt(3, criterioConciliacion.getOrden());
            st.registerOutParameter(4, java.sql.Types.INTEGER);
            st.execute();
            ConnectionUtils.commit(connection);
            result = st.getInt(4);
        } catch (SQLException e) {
            ConnectionUtils.rollback(connection);
            throw new DaoException(e);
        } finally {
            ConnectionUtils.closeStatement(st);
            ConnectionUtils.closeConnection(connection);
        }
        return result;
    }

    private void comun(PreparedStatement st, CriterioConciliacion criterioConciliacion) {
        try {
            st.setInt(1, criterioConciliacion.getCodBanco());
            st.setInt(2, criterioConciliacion.getCodTipoMovimientoBancario());
            st.setString(4, criterioConciliacion.getCriterioConciliacion());
            st.setString(5, criterioConciliacion.getEstado());
        } catch (SQLException e) {
            throw new DaoException(e);
        }
    }

    private CriterioConciliacion getData(ResultSet rs) throws SQLException {
        CriterioConciliacion result = null;
        result = new CriterioConciliacion(
                rs.getInt("CodBanco"),
                rs.getInt("CodTipoMovimientoBancario"),
                rs.getInt("Orden"),
                rs.getString("CriterioConciliacion"),
                rs.getString("Estado"));
        BancoDao bancoDao = new BancoDaoImpl();
        result.setBanco(bancoDao.selectPorCodBanco(result.getCodBanco()));
        return result;
    }
}
